﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entity;
using System.Data.SqlClient;
using DbCommon;
using System.Data;

namespace DAL
{
    public class OutterPhotosDAL
    {
        DbHelper dbhelper = new DbHelper();
        /// <summary>
        /// 添加一条记录
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public int Insert(BoOutterPhotos model)
        {
            model.OPHO_ID = Guid.NewGuid().ToString();
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("INSERT INTO [OutterPhotos]");
            sql.Append("([OPHO_ID],[OPHO_TITLE],[OPHO_PARTICIPANTS],[OPHO_URL],[OPHO_DATE])");
            sql.Append("VALUES");
            sql.Append("(@OPHO_ID,@OPHO_TITLE,@OPHO_PARTICIPANTS,@OPHO_URL,@OPHO_DATE)");
            return dbhelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }

        /// <summary>
        /// 修改一条记录
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public int Update(BoOutterPhotos model)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("UPDATE [OutterPhotos] SET ");
            sql.Append("OPHO_TITLE=@OPHO_TITLE");
            sql.Append(",OPHO_PARTICIPANTS=@OPHO_PARTICIPANTS");
            sql.Append(",OPHO_URL=@OPHO_URL");
            sql.Append(",OPHO_DATE=@OPHO_DATE");
            sql.Append(" WHERE ");
            sql.Append("OPHO_ID=@OPHO_ID");

            return dbhelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }

        /// <summary>
        /// 删除一条记录
        /// </summary>
        /// <returns></returns>
        public int Delete(string oPHO_ID)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("DELETE FROM OutterPhotos");
            sql.Append(" WHERE ");
            sql.Append("OPHO_ID=@OPHO_ID");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@OPHO_ID", SqlDbType.NVarChar, 50);
            parameter[0].Value = oPHO_ID;
            return dbhelper.ExcuteNoQuery(sql.ToString(), parameter);
        }

        /// <summary>
        /// 获取一条记录
        /// </summary>
        /// <returns></returns>
        public BoOutterPhotos GetModel(string oPHO_ID)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT OPHO_ID,OPHO_TITLE,OPHO_PARTICIPANTS,OPHO_URL,OPHO_DATE FROM OutterPhotos");
            sql.Append(" WHERE ");
            sql.Append("OPHO_ID=@OPHO_ID");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@OPHO_ID", SqlDbType.NVarChar, 50);
            parameter[0].Value = oPHO_ID;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoOutterPhotos> list = GetList(table);
            return list != null && list.Count > 0 ? list[0] : null;
        }

        public IList<BoOutterPhotos> GetStudentsPhotos(string contestId)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT OPHO_ID,OPHO_TITLE,OPHO_PARTICIPANTS,OPHO_URL,OPHO_DATE ");
            sql.Append(" FROM OutterPhotos AS O INNER JOIN Participants AS P ON O.OPHO_PARTICIPANTS=P.PAR_ID ");
            sql.Append(" WHERE P.PAR_STUDENTS=@PAR_STUDENTS");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@PAR_STUDENTS", SqlDbType.NVarChar, 50);
            parameter[0].Value = contestId;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoOutterPhotos> list = GetList(table);
            return list;
        }

        public IList<BoOutterPhotos> GetParticipantsPhotos(string procId)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT OPHO_ID,OPHO_TITLE,OPHO_PARTICIPANTS,OPHO_URL,OPHO_DATE ");
            sql.Append(" FROM OutterPhotos ");
            sql.Append(" WHERE OPHO_PARTICIPANTS=@OPHO_PARTICIPANTS");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@OPHO_PARTICIPANTS", SqlDbType.NVarChar, 50);
            parameter[0].Value = procId;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoOutterPhotos> list = GetList(table);
            return list;
        }

        public IList<BoOutterPhotos> GetAllPhotos()
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT OPHO_ID,OPHO_TITLE,OPHO_PARTICIPANTS,OPHO_URL,OPHO_DATE FROM OutterPhotos");
            DataTable table = dbhelper.ExcuteQuery(sql.ToString());
            IList<BoOutterPhotos> list = GetList(table);
            return list;
        }

        private IList<BoOutterPhotos> GetList(DataTable table)
        {
            List<BoOutterPhotos> list = new List<BoOutterPhotos>();
            if (table != null && table.Rows.Count > 0)
            {
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    BoOutterPhotos model = new BoOutterPhotos();
                    model.OPHO_ID = (string)table.Rows[i]["OPHO_ID"];
                    model.OPHO_TITLE = (string)table.Rows[i]["OPHO_TITLE"];
                    model.OPHO_PARTICIPANTS = (string)table.Rows[i]["OPHO_PARTICIPANTS"];
                    model.OPHO_URL = (string)table.Rows[i]["OPHO_URL"];
                    model.OPHO_DATE = (DateTime)table.Rows[i]["OPHO_DATE"];
                    list.Add(model);
                }
            }
            return list;
        }

        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        private SqlParameter[] GetParameters(BoOutterPhotos model)
        {
            SqlParameter[] parameters = new SqlParameter[] {
                new SqlParameter("@OPHO_ID",SqlDbType.NVarChar,50),
                new SqlParameter("@OPHO_TITLE",SqlDbType.NVarChar,50),
                new SqlParameter("@OPHO_PARTICIPANTS",SqlDbType.NVarChar,250),
                new SqlParameter("@OPHO_URL",SqlDbType.NVarChar,255),
                new SqlParameter("@OPHO_DATE",SqlDbType.DateTime,8),
            };
            parameters[0].Value = model.OPHO_ID;
            parameters[1].Value = model.OPHO_TITLE;
            parameters[2].Value = model.OPHO_PARTICIPANTS;
            parameters[3].Value = model.OPHO_URL;
            parameters[4].Value = model.OPHO_DATE;
            return parameters;
        }
    }
}